// quotes CRUD

var mysql = require('./db');

// <editor-fold defaultstate="collapsed" desc="readQuote = function(req, res)">
exports.readQuote = function(req, res) {

    //var sort = JSON.parse(req.query.sort)[0];

    var sql =
/*rows[0]*/ ' SELECT SQL_CALC_FOUND_ROWS qtd.id id,qtd.qo_id qo_id,qtd.cid cid,qtd.part_id part_id,qtd.eta eta,qtd.cust_id cust_id,' +
            ' qtd.sys_qty sys_qty,qtd.date date,qtd.dateStamp dateStamp,qtd.ship_method ship_method,' +
            ' qtd.salesperson salesperson,qtd.sales_email sales_email,qtd.sales_phone sales_phone,qtd.base_gp base_gp,' +
            ' qtd.option_gp option_gp,qtd.mfr_pn mfr_pn,qtd.part_desc part_desc,qtd.qty qty,qtd.cost cost,' +
            ' qtd.shipping shipping,qtd.deleted deleted,qtd.quote_name quote_name,qtd.config_notes config_notes,' +
            ' qtd.special_notes special_notes,qtd.EA EA,qtd.CustPO CustPO,qtd.TBD_SHIP TBD_SHIP,qtd.rowOrder rowOrder' +
//            ' ecp.profile_name profile_name,ecp.b_contact b_contact' +
            ' FROM exxact.quote_data qtd ';
//            ' LEFT JOIN exxact.system_users esy ON(qtd.cust_id=esy.id)' +
//            ' LEFT JOIN exxact.customer_profiles ecp ON(qtd.cust_id=ecp.id)' +
//            ' LEFT JOIN exxact.customer_profiles ec ON(ep.cat=ec.cat_id) ' + search +
//            ' LEFT JOIN exxact.customer_profiles ec ON(ep.cat=ec.cat_id) ' + search +
//            ' GROUP BY qo_id ORDER BY ' + sort.property + ' ' + sort.direction + ' LIMIT ' + req.query.start + ',' + req.query.limit + ';' +
/*rows[1]*/ 'SELECT FOUND_ROWS() total;';

    mysql.query(sql, function (err, rows) {
        if(err) {
            res.json({ success: false, crud: 'select', err: err });
        }
        else {
            res.json({ success: true, data: rows[0], total: eval(rows[1])[0].total });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="read = function(req, res)">
// $cid =  SELECT MAX(cid) as MAX_CID from quote_data where qo_id='$qo_id'"
// $quote_data = getQuoteData($qo_id, $cid); - "SELECT * FROM quote_data where qo_id='$id' and cid='$cid'";
// $cust_data = getCustData($cust_id); - "SELECT * FROM customer_profiles where id='$id'";
// $quote_cost = getQuoteTotalCost($qo_id, $cid); - "SELECT cost,qty FROM quote_data where qo_id='$id' and cid='$cid'"; return total $L1['cost'] * $L1['qty'];
exports.read = function(req, res) {

    var sort = JSON.parse(req.query.sort)[0];

    var sql =
/*rows[0]*/ ' SELECT SQL_CALC_FOUND_ROWS qtd.id id,qtd.qo_id qo_id,qtd.cid cid,qtd.part_id part_id,qtd.eta eta,qtd.cust_id cust_id,' +
            ' qtd.sys_qty sys_qty,qtd.date date,qtd.dateStamp dateStamp,qtd.ship_method ship_method,' +
            ' qtd.salesperson salesperson,qtd.sales_email sales_email,qtd.sales_phone sales_phone,qtd.base_gp base_gp,' +
            ' qtd.option_gp option_gp,qtd.mfr_pn mfr_pn,qtd.part_desc part_desc,qtd.qty qty,qtd.cost cost,' +
            ' qtd.shipping shipping,qtd.deleted deleted,qtd.quote_name quote_name,qtd.config_notes config_notes,' +
            ' qtd.special_notes special_notes,qtd.EA EA,qtd.CustPO CustPO,qtd.TBD_SHIP TBD_SHIP,qtd.rowOrder rowOrder,' +
            //' esy.fullname salesperson' +
            ' ecp.profile_name profile_name,ecp.b_contact b_contact' +
            ' FROM exxact.quote_data qtd ' +
            ' LEFT JOIN exxact.system_users esy ON(qtd.cust_id=esy.id)' +
            ' LEFT JOIN exxact.customer_profiles ecp ON(qtd.cust_id=ecp.id)' +
//            ' LEFT JOIN exxact.customer_profiles ec ON(ep.cat=ec.cat_id) ' + search +
//            ' LEFT JOIN exxact.customer_profiles ec ON(ep.cat=ec.cat_id) ' + search +
            ' GROUP BY qo_id ORDER BY ' + sort.property + ' ' + sort.direction + ' LIMIT ' + req.query.start + ',' + req.query.limit + ';' +
/*rows[1]*/ 'SELECT FOUND_ROWS() total;';

    mysql.query(sql, function (err, rows) {
        if(err) {
            res.json({ success: false, crud: 'select', err: err });
        }
        else {
            res.json({ success: true, data: rows[0], total: eval(rows[1])[0].total });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="add = function(req, res)">
exports.add = function(req, res) {
    //console.log('------post quotes insert');
    var param = this.parseQuoteParams(req.body);

    console.log('###INSERT INTO exxact.quote_data SET ' + param.sql);

    mysql.query('INSERT INTO exxact.quote_data SET ' + param.sql, function(err, result) {
        if(err) {
            res.json({ success: false, crud: 'insert', data: result });
        }
        else {
            result.id = result.insertId;
            res.json({ success: true, data: result });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="update = function(req, res)">
exports.update = function(req, res) {
    //console.log('------put quotes update');
    var param = this.parseQuoteParams(req.body);

    console.log('###UPDATE exxact.quote_data SET ' + param.sql + ' WHERE id=' + param.id);

    mysql.query('UPDATE exxact.quote_data SET ' + param.sql + ' WHERE id=?', [param.id], function(err, result) {
        if(err) {
            res.json({ success: false, crud: 'update', data: result });
        }
        else {
            res.json({ success: true, data: result });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="destroy = function(req, res)">
exports.destroy = function(req, res) {
    //console.log('------delete quotes delete');
    var id = req.body.id;
    console.log('###DELETE FROM exxact.quote_data WHERE id=' + id);

    mysql.query('DELETE FROM exxact.quote_data WHERE id=?', [id], function(err, result) {
        if(err) {
            res.json({ success: false, crud: 'delete', data: result });
        }
        else {
            res.json({ success: true, data: result });
        }
    });
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="parseQuoteParams = function(body)">
parseQuoteParams = function(body) {

    var params = eval(body);

    var sql = [];

    if(params.qo_id)         { sql.push('qo_id='          + params.qo_id); }
    if(params.cid)           { sql.push('cid='            + params.cid); }
    if(params.eta)           { sql.push('eta="'           + params.eta + '"'); }
    if(params.cust_id)       { sql.push('cust_id='        + params.cust_id); }
    if(params.sys_qty)       { sql.push('sys_qty='        + params.sys_qty); }
    if(params.date)          { sql.push('date="'          + params.date + '"'); }
    if(params.dateStamp)     { sql.push('dateStamp="'     + params.dateStamp + '"'); }
    if(params.ship_method)   { sql.push('ship_method="'   + params.ship_method + '"'); }
    if(params.salesperson)   { sql.push('salesperson="'   + params.salesperson + '"'); }
    if(params.sales_email)   { sql.push('sales_email="'   + params.sales_email + '"'); }
    if(params.sales_phone)   { sql.push('sales_phone="'   + params.sales_phone + '"'); }
    if(params.base_gp)       { sql.push('base_gp='        + params.base_gp); }
    if(params.option_gp)     { sql.push('option_gp='      + params.option_gp); }
    if(params.mfr_pn)        { sql.push('mfr_pn="'        + params.mfr_pn + '"'); }
    if(params.part_desc)     { sql.push('part_desc="'     + params.part_desc + '"'); }
    if(params.qty)           { sql.push('qty='            + params.qty); }
    if(params.cost)          { sql.push('cost='           + params.cost); }
    if(params.shipping)      { sql.push('shipping='       + params.shipping); }
    if(params.deleted)       { sql.push('deleted='        + params.deleted); }
    if(params.quote_name)    { sql.push('quote_name="'    + params.quote_name + '"'); }
    if(params.config_notes)  { sql.push('config_notes="'  + params.config_notes + '"'); }
    if(params.special_notes) { sql.push('special_notes="' + params.special_notes + '"'); }
    if(params.EA)            { sql.push('EA='             + params.EA); }
    if(params.CustPO)        { sql.push('CustPO='         + params.CustPO); }
    if(params.TBD_SHIP)      { sql.push('TBD_SHIP='       + params.TBD_SHIP); }
    if(params.rowOrder)      { sql.push('rowOrder='       + params.rowOrder); }

    return { sql: sql.join(), id: params.id };
};
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="CREATE TABLE exxact.quote_data">
/*
CREATE TABLE exxact.quote_data (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `qo_id` int(11) NOT NULL DEFAULT '0',
  `cid` int(11) NOT NULL DEFAULT '0',
  `eta` text NOT NULL,
  `cust_id` int(11) NOT NULL DEFAULT '0',
  `sys_qty` int(11) NOT NULL DEFAULT '0',
  `date` text NOT NULL,
  `dateStamp` date DEFAULT '0000-00-00',
  `ship_method` text NOT NULL,
  `salesperson` text NOT NULL,
  `sales_email` text NOT NULL,
  `sales_phone` text NOT NULL,
  `base_gp` double(10,2) DEFAULT '0.00',
  `option_gp` double(10,2) DEFAULT '0.00',
  `mfr_pn` text NOT NULL,
  `part_desc` text NOT NULL,
  `qty` int(11) NOT NULL,
  `cost` double(10,2) DEFAULT '0.00',
  `shipping` int(11) NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `quote_name` text NOT NULL,
  `config_notes` text NOT NULL,
  `special_notes` text NOT NULL,
  `EA` tinyint(1) NOT NULL DEFAULT '0',
  `CustPO` tinyint(1) NOT NULL DEFAULT '0',
  `TBD_SHIP` tinyint(1) NOT NULL DEFAULT '0',
  `rowOrder` int(11) NOT NULL DEFAULT '0' COMMENT 'Used for ordering Rows (or re-ordering)',
  PRIMARY KEY (`id`),
  KEY `qo_id` (`qo_id`)
) ENGINE=MyISAM AUTO_INCREMENT=403194 DEFAULT CHARSET=latin1


ALTER TABLE quote_data ADD INDEX cid(cid);
ALTER TABLE quote_data ADD INDEX deleted(deleted);

ALTER TABLE quote_data MODIFY COLUMN eta VARCHAR(64) NOT NULL DEFAULT '';
ALTER TABLE quote_data MODIFY COLUMN date VARCHAR(32) NOT NULL DEFAULT '';
ALTER TABLE quote_data MODIFY COLUMN ship_method VARCHAR(64) NOT NULL DEFAULT '';
ALTER TABLE quote_data MODIFY COLUMN salesperson VARCHAR(64) NOT NULL DEFAULT '';
ALTER TABLE quote_data MODIFY COLUMN sales_email VARCHAR(64) NOT NULL DEFAULT '';
ALTER TABLE quote_data MODIFY COLUMN sales_phone VARCHAR(64) NOT NULL DEFAULT '';
ALTER TABLE quote_data MODIFY COLUMN mfr_pn VARCHAR(32) NOT NULL DEFAULT '';
ALTER TABLE quote_data MODIFY COLUMN part_desc VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE quote_data MODIFY COLUMN quote_name VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE quote_data MODIFY COLUMN config_notes VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE quote_data MODIFY COLUMN special_notes VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE quote_data ADD COLUMN part_id INT(11) NOT NULL DEFAULT 0 AFTER cid;
ALTER TABLE quote_data ADD INDEX part_id(part_id);


CREATE TABLE exxact.quote_data (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `qo_id` int(11) NOT NULL DEFAULT '0',
  `cid` int(11) NOT NULL DEFAULT '0',
  `part_id` int(11) NOT NULL DEFAULT '0',
  `eta` varchar(64) NOT NULL DEFAULT '',
  `cust_id` int(11) NOT NULL DEFAULT '0',
  `sys_qty` int(11) NOT NULL DEFAULT '0',
  `date` varchar(32) NOT NULL DEFAULT '',
  `dateStamp` date DEFAULT '0000-00-00',
  `ship_method` varchar(64) NOT NULL DEFAULT '',
  `salesperson` varchar(64) NOT NULL DEFAULT '',
  `sales_email` varchar(64) NOT NULL DEFAULT '',
  `sales_phone` varchar(64) NOT NULL DEFAULT '',
  `base_gp` double(10,2) DEFAULT '0.00',
  `option_gp` double(10,2) DEFAULT '0.00',
  `mfr_pn` varchar(32) NOT NULL DEFAULT '',
  `part_desc` varchar(255) NOT NULL DEFAULT '',
  `qty` int(11) NOT NULL,
  `cost` double(10,2) DEFAULT '0.00',
  `shipping` int(11) NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `quote_name` varchar(255) NOT NULL DEFAULT '',
  `config_notes` varchar(255) NOT NULL DEFAULT '',
  `special_notes` varchar(255) NOT NULL DEFAULT '',
  `EA` tinyint(1) NOT NULL DEFAULT '0',
  `CustPO` tinyint(1) NOT NULL DEFAULT '0',
  `TBD_SHIP` tinyint(1) NOT NULL DEFAULT '0',
  `rowOrder` int(11) NOT NULL DEFAULT '0' COMMENT 'Used for ordering Rows (or re-ordering)',
  PRIMARY KEY (`id`),
  KEY `qo_id` (`qo_id`),
  KEY `cid` (`cid`),
  KEY `deleted` (`deleted`),
  KEY `part_id` (`part_id`)
) ENGINE=MyISAM AUTO_INCREMENT=403205 DEFAULT CHARSET=latin1


*/
// </editor-fold>
